package edu.example;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Collection;
import java.util.Iterator;
import java.util.TreeMap;
import java.util.Vector;

import javax.swing.JLabel;


public class ReviewManager 
{
	private static ReviewManager instance = null;
	
	private ApplicationManagerCloud am;
	private static UserManager um;
	
	private Review currentReview ;
	private Review r ;
	private Reviewer currentReviewer ;
	private Application currentApplication ;
	
	private Vector<Review> allReivews = new Vector<Review>();
	private Vector<String> dbreader = new Vector<String>();
	private int numOfReviews;
	private String Content;
	private double Rating;
	private String lastModified;
	
	public static ReviewManager getRvManager()
	{
		 if (instance == null) 
			 instance = new ReviewManager(um);
		 return instance;
	 }
	
	public ReviewManager(UserManager initUM)
	{
		um = initUM;
		getDBReviews();
	}
	
	
	public void getDBReviews()
	{
		//Read from DB
		//Read Reviewer E-mail Address
		//Then call getReviewerList(), find E-mail Matching
		//Construct or get Reviewer OBject
		//Read Application E-mail Address
		//Then call ApplicationManager.getAllApp().get(E-mail)
		//Construct or get Application OBject
		//Read Content
		//Read Rating, parse to Double
		//Read lastModified
		//Construct the review
		//Append all reviews from DB to allReivews
		dbreader= new Vector<String>();
		//um=um.getURManager();
		am=am.getAppManager();
		try{
			Connection conn=ConnectionUtils.getConnection();
			PreparedStatement s=conn.prepareStatement("select * from reviews",
	                    ResultSet.TYPE_SCROLL_SENSITIVE,
	                    ResultSet.CONCUR_UPDATABLE);
	    	ResultSet rs=s.executeQuery();
	    	String Stringreader = null;
	    	//int reviewercolumn=8;
	    	//int Ratingcolumn=13;
	    	//int lastModifiedcolumn=23;
	    	//int Contentcolumn=18;
	    	
	        if(rs.next()){
            	rs.beforeFirst();
            	while(rs.next()){     
            		currentReview = new Review();
            		
	            	Stringreader = rs.getString(2);
	            	Stringreader = new String(Stringreader.getBytes("ISO-8859-1"),"GB2312");        		            	               
	            	
	            	for(int i=0;i<um.getReviewerList().size();i++)
	            		if(Stringreader.equals(um.getReviewerList().get(i).getEMailAddress())){
	            			currentReviewer=um.getReviewerList().get(i);
	            		}
	            	
	            	Stringreader = rs.getString(1);
	            	Stringreader = new String(Stringreader.getBytes("ISO-8859-1"),"GB2312");
	            	Collection<Application> temp = am.getAllApps().values();
	    			Iterator<Application> it = temp.iterator();
	    				
	    			while(it.hasNext())
	    			{
	    				Application tempApp = it.next();
	    				if(Stringreader.equals(tempApp.getEmailAddress()))
	            			currentApplication=tempApp;
	    			}
	            	
	            	
	            	Content = rs.getString(4);
	            	Content = new String(Content.getBytes("ISO-8859-1"),"GB2312");
	            		            
	            	Rating = Double.parseDouble(rs.getString(3));
	            	
	            	lastModified = rs.getString(5);
	            	lastModified = new String(lastModified.getBytes("ISO-8859-1"),"GB2312");
	            	
	            	currentReview = new Review(currentReviewer,currentApplication);
	            	currentReview.setcontent(Content);
	            	currentReview.setRating(Rating);
	            	currentReview.setLastModified(lastModified);
	            	
	            	allReivews.addElement(currentReview);
	            }	
            	System.out.println("all reviews are loaded");
            }
	        rs.close();
		    conn.close();
	            
			}catch(Exception e){
	    		e.printStackTrace();
	    	}
		System.out.println("all reviews uploaded from db");
	}
	
	public Vector<Review> getReviewsByApp(Application app)
	{
		Vector<Review> reivews = new Vector<Review>();
		if(!allReivews.isEmpty())
		{
			for(int i = 0; i < allReivews.size(); i++)
			{
				if(allReivews.get(i).getApplication().getEmailAddress() == app.getEmailAddress())
				{
					reivews.add(allReivews.get(i));
				}
			}
		}
		return reivews;
	}
	
	public void addReview(Review R)
	{
		allReivews.add(R);
	}
	
	public void saveToDB()
	{
		if(!allReivews.isEmpty())
		{
			for(int i = 0; i< allReivews.size(); i++)
			{
				//System.out.println(allReivews.get(i).getReviewer().getEMailAddress());
				//System.out.println(allReivews.get(i).getApplication().getEmailAddress());
				//System.out.println(allReivews.get(i).getcontent());
				//System.out.println(allReivews.get(i).getRating());
				//System.out.println(allReivews.get(i).getLastModified());
				
				r=allReivews.get(i);
				System.out.println("1 row of review is updated in db");
				try{
				    Connection conn=ConnectionUtils.getConnection();
				    PreparedStatement s=conn.prepareStatement("select count(*) from reviews where EMAIL_ADDRESS= ?");
				    s.setString(1, r.getApplication().getEmailAddress());
				    ResultSet rs = s.executeQuery();
				    if(rs.next()){
				    	if(rs.getInt(1)>0){				  
				    		rs.close();
				    		PreparedStatement ss=conn.prepareStatement("DELETE from reviews where EMAIL_ADDRESS= ?");
				    		ss.setString(1, r.getApplication().getEmailAddress());
				    		int deletecount = ss.executeUpdate();
				    		System.out.println(deletecount + " row of reviews data is updated");
				    	}				    	    
				    }
			    
				    conn.setAutoCommit(false);
				    String checker;
				    PreparedStatement stmt = conn.prepareStatement("INSERT INTO reviews VALUES ( ?,?,?,?,?)");
			        
			        stmt.setString(1,  r.getApplication().getEmailAddress());
			        stmt.setString(2,  r.getReviewer().getEMailAddress());
			        stmt.setString(3,  Double.toString(r.getRating()));
			        stmt.setString(4,  r.getcontent());
			        stmt.setString(5,  r.getLastModified());
			        
			        //checker=CurrentDataEntry.get(i);
			       //if(checker==null)
			        	//stmt.setString(i+1,  "");    			        	
			    
			    stmt.addBatch();
			    //System.out.println(CurrentDataEntry.size());
			    // submit the batch for execution
		        int[] updateCounts = stmt.executeBatch();
		        System.out.println("reviews are updated");
		        conn.commit(); 
		        conn.close();
				}catch(Exception e){
		    		e.printStackTrace();
		    	}	
				
			}
			
		}
	}
	
	public int getNumOfReviews()
	{
		return allReivews.size();
	}
	
	
	public void updateUser(Reviewer r){
		
		try{
		    Connection conn=ConnectionUtils.getConnection();
		    PreparedStatement s=conn.prepareStatement("select count(*) from reviewer where username= ?");
		    s.setString(1, r.getName());
		    ResultSet rs = s.executeQuery();
		    if(rs.next()){
		    	if(rs.getInt(1)>0){				  
		    		rs.close();
		    		PreparedStatement ss=conn.prepareStatement("DELETE from reviewer where username= ?");
		    		ss.setString(1, r.getName());
		    		int deletecount = ss.executeUpdate();
		    		System.out.println(deletecount + " row of user data is updated");
		    	}				    	    
		    }
	    
		    conn.setAutoCommit(false);
		    String checker;
		    PreparedStatement stmt = conn.prepareStatement("INSERT INTO reviewer VALUES (?, ?,?,?,?,?, ?,?,?,?,?, ?,?)");
	        
	        stmt.setString(1,  r.getName());
	        boolean ChairCheck=r.getChairCheck();
	        if(ChairCheck=true)
	        	stmt.setString(2,  "chair");
	        else stmt.setString(2,  "reviewer");
	        stmt.setString(3,  r.getPassword());
	        stmt.setString(4,  r.getEMailAddress());
	        stmt.setString(5,  r.getHomeCountry1());
	        stmt.setString(6,  r.getHomeCountry2());
	        stmt.setString(7,  r.getResearchArea1());
	        stmt.setString(8,  r.getResearchArea2());
	        stmt.setString(9,  r.getResearchArea3());	        
	        stmt.setString(10,  Integer.toString(r.getNumAssignedReviews()));
	        boolean availableCheck=r.getAvailable();
	        if(availableCheck=true)
	        	stmt.setString(11,  "true");
	        else stmt.setString(11,  "true");
	        stmt.setString(12,  Float.toString(r.getWorkload()));
	        stmt.setString(13,  Integer.toString(r.getCompletedReview()));
	        
	        //checker=CurrentDataEntry.get(i);
	       // if(checker==null)
	        	//stmt.setString(i+1,  "");    			        	
	    
	    stmt.addBatch();
	    //System.out.println(CurrentDataEntry.size());
	    // submit the batch for execution
        int[] updateCounts = stmt.executeBatch();
        conn.commit(); 
        conn.close();
		}catch(Exception e){
    		e.printStackTrace();
    	}	
	}
	
	public static void main(String[] args)
	{
		File file1 = new File("AY.txt");
		File file2 = new File("AY-map.txt");
		File file3 = new File("OTS.txt");
		File file4 = new File("OTS-map.txt");
		ApplicationManagerCloud apm = new ApplicationManagerCloud(null, file1, file2, file3, file4);
		//System.out.println(formatter.toString());
		//System.out.println(formatter.getApps().get("maurice.wilkes@bmail.com").getApplicantLastName());
	}
}
